"""
数据库模型定义

定义策略执行、交易记录、回测结果等数据的持久化模型
"""

from datetime import datetime
from typing import Dict, Any, Optional
from sqlalchemy import Column, String, Integer, Float, DateTime, Text, Boolean, ForeignKey, JSON
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
import uuid

Base = declarative_base()


class Strategy(Base):
    """策略模型"""
    __tablename__ = 'strategies'
    
    id = Column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    name = Column(String(100), nullable=False)
    type = Column(String(50), nullable=False)  # grid, dca, etc.
    symbol = Column(String(20), nullable=False)
    exchange = Column(String(50), nullable=False)
    market_type = Column(String(20), nullable=False)  # spot, futures
    config = Column(JSON, nullable=False)
    status = Column(String(20), default='stopped')  # running, stopped, paused
    created_at = Column(DateTime, default=datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    
    # 关联关系
    orders = relationship("Order", back_populates="strategy", cascade="all, delete-orphan")
    trades = relationship("Trade", back_populates="strategy", cascade="all, delete-orphan")
    performance_records = relationship("StrategyPerformance", back_populates="strategy", cascade="all, delete-orphan")


class Order(Base):
    """订单模型"""
    __tablename__ = 'orders'
    
    id = Column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    strategy_id = Column(String(36), ForeignKey('strategies.id'), nullable=False)
    exchange_order_id = Column(String(100))  # 交易所返回的订单ID
    symbol = Column(String(20), nullable=False)
    side = Column(String(10), nullable=False)  # buy, sell
    type = Column(String(20), nullable=False)  # market, limit, stop
    amount = Column(Float, nullable=False)
    price = Column(Float)  # 限价单价格
    filled_amount = Column(Float, default=0.0)
    filled_price = Column(Float, default=0.0)
    status = Column(String(20), nullable=False)  # pending, filled, cancelled, failed
    fee = Column(Float, default=0.0)
    fee_currency = Column(String(10))
    grid_level = Column(Integer)  # 网格层级
    created_at = Column(DateTime, default=datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    
    # 关联关系
    strategy = relationship("Strategy", back_populates="orders")
    trades = relationship("Trade", back_populates="order", cascade="all, delete-orphan")


class Trade(Base):
    """交易记录模型"""
    __tablename__ = 'trades'
    
    id = Column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    order_id = Column(String(36), ForeignKey('orders.id'), nullable=False)
    strategy_id = Column(String(36), ForeignKey('strategies.id'), nullable=False)
    exchange_trade_id = Column(String(100))  # 交易所返回的交易ID
    symbol = Column(String(20), nullable=False)
    side = Column(String(10), nullable=False)  # buy, sell
    amount = Column(Float, nullable=False)
    price = Column(Float, nullable=False)
    fee = Column(Float, default=0.0)
    fee_currency = Column(String(10))
    profit_loss = Column(Float, default=0.0)  # 盈亏
    executed_at = Column(DateTime, default=datetime.utcnow)
    
    # 关联关系
    order = relationship("Order", back_populates="trades")
    strategy = relationship("Strategy", back_populates="trades")


class StrategyPerformance(Base):
    """策略性能记录模型"""
    __tablename__ = 'strategy_performance'
    
    id = Column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    strategy_id = Column(String(36), ForeignKey('strategies.id'), nullable=False)
    date = Column(DateTime, nullable=False)
    
    # 基础指标
    total_return = Column(Float, default=0.0)  # 总收益率
    daily_return = Column(Float, default=0.0)  # 日收益率
    cumulative_return = Column(Float, default=0.0)  # 累计收益率
    
    # 风险指标
    max_drawdown = Column(Float, default=0.0)  # 最大回撤
    volatility = Column(Float, default=0.0)  # 波动率
    sharpe_ratio = Column(Float, default=0.0)  # 夏普比率
    
    # 交易统计
    total_trades = Column(Integer, default=0)  # 总交易次数
    winning_trades = Column(Integer, default=0)  # 盈利交易次数
    losing_trades = Column(Integer, default=0)  # 亏损交易次数
    win_rate = Column(Float, default=0.0)  # 胜率
    
    # 资金统计
    initial_balance = Column(Float, default=0.0)  # 初始资金
    current_balance = Column(Float, default=0.0)  # 当前资金
    total_profit = Column(Float, default=0.0)  # 总盈利
    total_loss = Column(Float, default=0.0)  # 总亏损
    
    # 其他指标
    avg_profit_per_trade = Column(Float, default=0.0)  # 平均每笔盈利
    avg_loss_per_trade = Column(Float, default=0.0)  # 平均每笔亏损
    profit_factor = Column(Float, default=0.0)  # 盈利因子
    
    created_at = Column(DateTime, default=datetime.utcnow)
    
    # 关联关系
    strategy = relationship("Strategy", back_populates="performance_records")


class BacktestResult(Base):
    """回测结果模型"""
    __tablename__ = 'backtest_results'
    
    id = Column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    strategy_name = Column(String(100), nullable=False)
    strategy_config = Column(JSON, nullable=False)
    symbol = Column(String(20), nullable=False)
    exchange = Column(String(50), nullable=False)
    
    # 回测参数
    start_date = Column(DateTime, nullable=False)
    end_date = Column(DateTime, nullable=False)
    initial_balance = Column(Float, nullable=False)
    
    # 回测结果
    final_balance = Column(Float, nullable=False)
    total_return = Column(Float, nullable=False)
    annual_return = Column(Float, nullable=False)
    max_drawdown = Column(Float, nullable=False)
    sharpe_ratio = Column(Float, nullable=False)
    volatility = Column(Float, nullable=False)
    
    # 交易统计
    total_trades = Column(Integer, default=0)
    winning_trades = Column(Integer, default=0)
    losing_trades = Column(Integer, default=0)
    win_rate = Column(Float, default=0.0)
    
    # 详细数据（JSON格式存储）
    trades_data = Column(JSON)  # 交易记录
    equity_curve = Column(JSON)  # 资金曲线
    performance_metrics = Column(JSON)  # 详细性能指标
    
    created_at = Column(DateTime, default=datetime.utcnow)


class AccountBalance(Base):
    """账户余额模型"""
    __tablename__ = 'account_balances'
    
    id = Column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    exchange = Column(String(50), nullable=False)
    currency = Column(String(10), nullable=False)
    free_balance = Column(Float, default=0.0)  # 可用余额
    locked_balance = Column(Float, default=0.0)  # 冻结余额
    total_balance = Column(Float, default=0.0)  # 总余额
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)


class SystemMetrics(Base):
    """系统指标模型"""
    __tablename__ = 'system_metrics'
    
    id = Column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    metric_name = Column(String(50), nullable=False)  # cpu_usage, memory_usage, etc.
    metric_value = Column(Float, nullable=False)
    metric_unit = Column(String(20))  # %, MB, etc.
    timestamp = Column(DateTime, default=datetime.utcnow)


class Alert(Base):
    """告警模型"""
    __tablename__ = 'alerts'
    
    id = Column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    level = Column(String(20), nullable=False)  # info, warning, error, critical
    title = Column(String(200), nullable=False)
    message = Column(Text, nullable=False)
    source = Column(String(50))  # strategy, system, exchange
    strategy_id = Column(String(36), ForeignKey('strategies.id'))
    status = Column(String(20), default='active')  # active, acknowledged, resolved
    created_at = Column(DateTime, default=datetime.utcnow)
    resolved_at = Column(DateTime)


class DataFile(Base):
    """数据文件记录模型"""
    __tablename__ = 'data_files'
    
    id = Column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    exchange = Column(String(50), nullable=False)
    market_type = Column(String(20), nullable=False)  # spot, futures
    symbol = Column(String(20), nullable=False)
    interval = Column(String(10), nullable=False)  # 1m, 5m, 1h, etc.
    file_path = Column(String(500), nullable=False)
    file_size = Column(Integer, default=0)  # 文件大小（字节）
    record_count = Column(Integer, default=0)  # 记录数量
    start_time = Column(DateTime)  # 数据开始时间
    end_time = Column(DateTime)  # 数据结束时间
    created_at = Column(DateTime, default=datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)